package com.eascs.web.o2o.web.reward.service.impl;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StringUtil;
import com.eascs.site.view.BaseRestResult;
import com.eascs.web.o2o.web.reward.dao.BaseDao;
import com.eascs.web.o2o.web.reward.entity.LoanDetail;
import com.eascs.web.o2o.web.reward.entity.LoanDetailForm;
import com.eascs.web.o2o.web.reward.service.ReportForLoanDetailService;
import com.eascs.web.sso.User;
import com.eascs.web.sso.UserHolder;

@Service
public class ReportForLoanDetailServiceImpl implements ReportForLoanDetailService {
	
	private static Logger log = LoggerFactory.getLogger(OverdualReportServiceImpl.class);

	@Autowired
	BaseDao baseDao;

	@Override
	public PageResult<Object[]> reportForLoanDetail(LoanDetailForm entity, String type) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(),
				entity.getShowPage());
		StringBuffer querySql = new StringBuffer();
		if (type.equals("search")) {
			querySql.append("select t_month,province_name,bill_code,loaner_name,"
					+ "date_format(loan_date,'%Y-%m-%d') loan_date,"
					+ "date_format(end_bank_date,'%Y-%m-%d') end_bank_date,"
					+ "loan_amount,loan_term,case when is_continue_loan='Y' then '是' "
					+ "when is_continue_loan='N' then '否' end,convert_loan_amount,operator_name,"
					+ "recommend_type,referrer_name,recommend_coefficient,"
					+ "operator_loan_amount,recommend_loan_amount,remark " 
					+ "from dw_reward_loan_data_detail "
					+ "where /**DATA_PERMISSION(ltd)**/ 1=1 ");
			sqlPageQuery.setQuerySql(querySql.toString());
			if (!StringUtil.isEmpty(entity.getCheckMonth())) {
				sqlPageQuery.setQuerySql(sqlPageQuery.getQuerySql() + " and t_month=? ");
				sqlPageQuery.addParam(entity.getCheckMonth());
			} 
			if (!StringUtil.isEmpty(entity.getProvinceName())) {
				sqlPageQuery.setQuerySql(sqlPageQuery.getQuerySql() + " and province_name=?");
				sqlPageQuery.addParam(entity.getProvinceName());
			}
			sqlPageQuery.setQuerySql(sqlPageQuery.getQuerySql() + " order by loan_date DESC");
		} else if (type.equals("download")) {
			querySql.append(
					"select t_month,province_code,province_name,bill_code,loaner_name,"
					+ "loan_date,end_bank_date,settle_date,"
					+ "loan_amount,loan_term,case when is_continue_loan='Y' then '是' "
					+ "when is_continue_loan='N' then '否' end,convert_loan_amount,"
					+ "operator_no,operator_name,recommend_type,"
					+ "referrer_no,referrer_name,recommend_coefficient,"
					+ "operator_loan_amount,recommend_loan_amount,remark,"
					+ "ltd,ltd_code,ltd_name "
					+ "from dw_reward_loan_data_detail "
					+ "where t_month = date_format(date_sub(now(),interval 1 month),'%Y-%m') "
					+ "order by loan_date DESC");
			sqlPageQuery.setQuerySql(querySql.toString());
		}
		return baseDao.findDataWithPage(sqlPageQuery);
	}

	@Override
	@Transactional("transactionManagerDbForReward")
	public BaseRestResult<String> addLoanDeatilList(List<List<Object>> result) {
		BaseRestResult<String> results = BaseRestResult.info("导入成功").andRefresh();
		User user=UserHolder.getUser();
		//删除当前月份前一个月的所有数据
		baseDao.executeSql(" delete from dw_reward_loan_data_detail where  t_month = DATE_FORMAT(DATE_SUB(now(),INTERVAL 1 month),'%Y-%m')");
		//将excel中的数据更新到数据库中
		if(result!=null&&result.size()>0){
			for (int i = 0; i < result.size(); i++) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				List<Object> list = (List) result.get(i);
				LoanDetail loanDetail = new LoanDetail();
				loanDetail.setT_month(list.get(0).toString());
				loanDetail.setProvince_code(list.get(1).toString());
				loanDetail.setProvince_name(list.get(2).toString());
				loanDetail.setBill_code(list.get(3).toString());
				loanDetail.setLoaner_name(list.get(4).toString());
				loanDetail.setLoan_date((Date)list.get(5));
				loanDetail.setEnd_bank_date((Date)list.get(6));
				loanDetail.setSettle_date(list.get(7) == null ? null :(Date)list.get(7));
				loanDetail.setLoan_amount((BigDecimal)list.get(8));
				loanDetail.setLoan_term(list.get(9).toString());
				if(list.get(10).toString().contains("是") && !list.get(10).toString().contains("否")) {
					loanDetail.setIs_continue_loan("Y");
				} else if(list.get(10).toString().contains("否") && !list.get(10).toString().contains("是")) {
					loanDetail.setIs_continue_loan("N");
				} 
				loanDetail.setConvert_loan_amount((BigDecimal)list.get(11));
				loanDetail.setOperator_no(list.get(12).toString());
				loanDetail.setOperator_name(list.get(13).toString());
				loanDetail.setRecommend_type(list.get(14).toString());
				loanDetail.setReferrer_no(list.get(15) == null ? null : list.get(15).toString());
				loanDetail.setReferrer_name(list.get(16) == null ? null : list.get(16).toString());
				loanDetail.setRecommend_coefficient(list.get(17).toString());
				loanDetail.setOperator_loan_amount((BigDecimal)list.get(18));
				loanDetail.setRecommend_loan_amount((BigDecimal)list.get(19));
				loanDetail.setRemark(list.get(20) == null ? null : list.get(20).toString());
				loanDetail.setLtd(list.get(21) == null ? null : list.get(21).toString());
				loanDetail.setLtd_code(list.get(22) == null ? null : list.get(22).toString());
				loanDetail.setLtd_name(list.get(23) == null ? null : list.get(23).toString());
				loanDetail.setIs_lock("N");
				loanDetail.setCreate_user(user.getCname());
				loanDetail.setCreate_date(new Date());
				loanDetail.setUpdate_user(user.getCname());
				loanDetail.setUpdate_date(new Date());
				loanDetail.setConfirm_user(user.getCname());
				loanDetail.setConfirm_date(new Date());
				//loanDetail.setId(getIdByBillCode(list.get(3).toString()));
				//baseDao.updateEntity(loanDetail);
				baseDao.saveEntity(loanDetail);
			}
		}else{
			log.error("导入的数据为空，事务回滚");
			throw new RuntimeException("导入的数据为空，请重新导入！");
		}
	   return results;
	}

	public String getIdByBillCode(String billCode) {
		List<Object[]> list = baseDao.executeSqlForObjectArray("select id from dw_reward_loan_data_detail "
				+ "where bill_code = '" + billCode + "'");
		return list.get(0)[0].toString();
	}

}
